/****** Object:  Stored Procedure dbo.CGO_GameEnrollmentGetListByUser    Script Date: Sunday, March 06, 2011 ******/
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'dbo.CGO_GameEnrollmentGetListByUser')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
  DROP PROCEDURE dbo.CGO_GameEnrollmentGetListByUser
GO


/* -------------------------------------------------------------------------------------
/   GameEnrollmentGetList 
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.CGO_GameEnrollmentGetListByUser
  @PortalID INT ,    
  @UserID INT ,  
  @GetCount BIT = 0,
  @PageSize INT = 10,
  @CurrentPage INT = 1
AS 
  DECLARE @TotalRowCount INT
  DECLARE @EnrollStatus_ENROLLED INT  
  SET @TotalRowCount = 0
  SELECT  @EnrollStatus_ENROLLED = StatusID
  FROM    dbo.CGO_GameEnrollmentStatus
  WHERE   StatusCode = 'ENROLLED'
  
  IF @UserID IS NULL 
    BEGIN
      SET @UserID = 0
    END
  IF @GetCount IS NULL
    BEGIN
    	SET @GetCount = 0
    END
  IF @PageSize IS NULL
    BEGIN
  	  SET @PageSize = 10
    END
  IF @CurrentPage IS NULL
    BEGIN
  	  SET @CurrentPage = 1
    END
  
  IF @GetCount = 1
  BEGIN
    SELECT @TotalRowCount = COUNT(*)
    FROM   dbo.CGO_GameEnrollment M
    WHERE  M.PortalID = @PortalID
          AND ( @UserID = 0
                OR M.UserID = @UserID
              )
          AND ( M.EnrollStatusID <> @EnrollStatus_ENROLLED )
  END
  
  SET ROWCOUNT @PageSize
  
	SELECT  @TotalRowCount AS TotalRowCount, T.*
  FROM    ( SELECT  ROW_NUMBER() OVER ( ORDER BY M.LastUpdateDate DESC ) AS RANK ,
                    EnrollmentID ,
                    M.PortalID ,
                    M.EventID ,
                    E.Name AS EventName ,
                    M.GameID ,
                    G.Name AS GameName ,
                    M.UserID ,
                    U.Username ,
                    EnrollStatusID ,
                    S.StatusDesc AS EnrollStatus ,
                    M.EnrollFee ,
                    StartTime ,
                    EndTime ,
                    ConnectCount ,
                    Score ,
                    M.CreatedDate ,
                    M.LastUpdateDate
            FROM    dbo.CGO_GameEnrollment M
                    LEFT JOIN dbo.CGO_GameEnrollmentStatus S ON M.EnrollStatusID = S.StatusID
                    LEFT JOIN dbo.Users U ON M.UserID = U.UserID
                    LEFT JOIN dbo.CGO_Game G ON M.GameID = G.GameID
                    LEFT JOIN dbo.CGO_Event E ON M.EventID = E.EventID
            WHERE   M.PortalID = @PortalID
                    AND ( @UserID = 0
                          OR M.UserID = @UserID
                        )
                    AND ( M.EnrollStatusID <> @EnrollStatus_ENROLLED )
          ) T
  WHERE   T.[RANK] > ( @CurrentPage - 1 ) * @PageSize  
  
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO